# code = 0  # 请求成功
# code = 1  # 返回失败数据
# code = 2  # 数据库操作异常
import pymysql
import cx_Oracle
from django.core.paginator import Paginator
from django.db import transaction
from django.http import JsonResponse
from django.shortcuts import render
from shujuyuan.models import TdsdDataSource, TdsdFtpSource
from utils import util
from utils.util import request_body_serialize, FormVerify
from yonghuguanli.models import TdsdUser
from ftplib import FTP

fv = FormVerify()


@util.user_login
def source_manage(request):
    """登录之后方可查看此页面，数据由前端异步获取"""
    level_id = request.session.get('level_id')  # 前端用来控制是否显示每个数据源的创建者，超级管理员可以看所有资源包括创建者
    context = {'title': '数据库源管理', 'level_id': level_id}
    return render(request, 'source_manage.html', context)


@util.user_login
def ftp_source(request):
    """FTP数据源管理页面"""
    level_id = request.session.get('level_id')  # 前端用来控制是否显示每个数据源的创建者，超级管理员可以看所有资源包括创建者
    context = {'title': 'FTP源管理', 'level_id': level_id}
    return render(request, 'ftp_source.html', context)


@util.user_login
def ftp_source_info(request):
    """ftp数据源信息的增删改查操作"""
    user_id = request.session.get('user_id')
    level_id = request.session.get('level_id')
    if request.method == 'POST':  # 新增数据源
        arg = request.POST
        ftp_name = arg.get('ftp_name')
        # 对资源名称格式进行验证
        if not fv.verify_source_name(ftp_name):
            return JsonResponse({'code': 1, 'msg': 'FTP名称只能为数字字母汉字，且不能以数字开头'})
        # FTP名称和用户联合唯一
        _source = TdsdFtpSource.objects.filter(isdelete=0, user_id=user_id, ftp_name=ftp_name)
        if _source.count() > 0:  # 说明此用户名下已有此数据源名称
            return JsonResponse({'code': 1, 'msg': 'FTP名称已存在'})
        ftp_ip = arg.get('ftp_ip')
        # 对ip地址进行验证
        if not fv.verify_ip(ftp_ip):
            return JsonResponse({'code': 1, 'msg': 'ip地址格式有误'})
        ftp_port = arg.get('ftp_port')  # 必填
        if not fv.verify_port(ftp_port):
            return JsonResponse({'code': 1, 'msg': '端口号格式有误'})
        ftp_username = arg.get('ftp_username')  # ftp服务器的用户名，必填
        ftp_password = arg.get('ftp_password')  # ftp服务器的密码，必填
        ftp_schema = arg.get('ftp_schema')  # ftp模式
        if not all([ftp_name, ftp_ip, ftp_port, ftp_username, ftp_password, ftp_schema]):
            return JsonResponse({'code': 1, 'msg': '必填项不能为空'})
        # 数据保存
        try:
            with transaction.atomic():  # 开启事务，失败会自动回滚，并执行except中的代码
                source = TdsdFtpSource.objects.create(
                    ftp_name=ftp_name,
                    ftp_ip=ftp_ip,
                    ftp_port=ftp_port,
                    ftp_username=ftp_username,
                    ftp_password=ftp_password,
                    ftp_schema=ftp_schema,
                    user_id=user_id,  # 登录之后才有值，否则为None
                )
                return JsonResponse({'code': 0, 'msg': 'Success'})
        except Exception as e:
            return JsonResponse({'code': 2, 'msg': str(e)})
    elif request.method == 'GET':
        # 获取所有数据源列表返回前端展示，数据表格的异步请求，根据用户level_id等级显示不同内容，普通用户看到自己创建的，超级管理员看到所有的
        arg = request.GET
        search_info = arg.get('search_info')  # 搜索内容，ftp源名称
        current_page = arg.get('page')  # 当前页码
        limit = arg.get('limit')  # 限制条目数
        if level_id == 0:  # 超级管理员
            if search_info:
                # 处理搜索内容的数据返回
                # 超级管理员不当看到已经逻辑删除的数据源
                sources_obj = TdsdFtpSource.objects.filter(isdelete=0, ftp_name__contains=search_info).order_by(
                    '-update_time')
            else:
                sources_obj = TdsdFtpSource.objects.filter(isdelete=0).order_by('-update_time')
        else:
            if search_info:
                sources_obj = TdsdFtpSource.objects.filter(isdelete=0, ftp_name__contains=search_info, user_id=user_id).order_by(
                    '-update_time')
            else:
                sources_obj = TdsdFtpSource.objects.filter(isdelete=0, user_id=user_id).order_by(
                    '-update_time')  # 根据用户id获取名下未删除的数据源信息
        count = sources_obj.count()
        if count > 0:  # 有数据，返回
            paginator = Paginator(sources_obj, limit)
            # 得到当前页的数据
            page = paginator.page(current_page)
            data = []
            for i, source in enumerate(page.object_list):
                item = dict()
                # role_id和dir_id的用途是编辑时数据回显
                item['id'] = i + 1  # 用于前端展示的编号
                item['ftp_id'] = source.ftp_id  # 数据库真实id编号
                item['ftp_name'] = source.ftp_name
                item['ftp_ip'] = source.ftp_ip
                item['ftp_port'] = source.ftp_port
                item['ftp_schema'] = str(source.ftp_schema)
                if source.ftp_schema == 0:
                    item['ftp_schema_info'] = '主动模式(PORT)'  # 点击编辑按钮，数据回显时，根据对应的value显示mysql或oracle
                elif source.ftp_schema == 1:
                    item['ftp_schema_info'] = '被动模式(PASV)'
                user_id_source = source.user_id  # 每条资源所属的用户id
                # 每条资源所属的用户名，有可能用户id不存在，清库的情况
                user = TdsdUser.objects.filter(id=user_id_source).first()
                creator = user.username if user else None
                item['creator'] = creator  # 用户的用户名
                item['update_time'] = str(source.update_time)
                item['ftp_username'] = source.ftp_username  # 数据库的用户名
                item['ftp_password'] = source.ftp_password  # 数据库的密码
                data.append(item)
            resp = {"code": 0, "count": count, "data": data}
        else:  # 没有数据
            resp = {'code': 1, 'msg': '暂无数据源!'}
        return JsonResponse(resp)
    elif request.method == 'DELETE':
        # 逻辑删除数据源信息
        arg = request_body_serialize(request)
        ftp_id = arg.get('ftp_id')
        # 从数据源表中找到对应的信息,然后逻辑删除
        source = TdsdFtpSource.objects.filter(ftp_id=ftp_id, isdelete=0)  # 已经删除过不能再次删除
        if len(source) == 1:
            try:
                with transaction.atomic():
                    source.update(isdelete=1)
                    code = 0
                    msg = 'Success'
            except Exception as e:
                code = 2  # 数据库操作异常
                msg = str(e)
        else:
            msg = 'FTP数据源不存在'
            code = 1
        resp = {'code': code, 'msg': msg}
        return JsonResponse(resp)
    elif request.method == 'PUT':
        # 数据源更改操作
        arg = request_body_serialize(request)
        ftp_name = arg.get('ftp_name')
        if not fv.verify_source_name(ftp_name):
            return JsonResponse({'code': 1, 'msg': '数据源名称只能为数字字母汉字，且不能以数字开头'})
        ftp_id = int(arg.get('ftp_id')) if arg.get('ftp_id').isdigit() else None
        ftp_ip = arg.get('ftp_ip')
        if not fv.verify_ip(ftp_ip):
            return JsonResponse({'code': 1, 'msg': 'ip地址格式有误'})
        ftp_port = arg.get('ftp_port')
        if not fv.verify_port(ftp_port):
            return JsonResponse({'code': 1, 'msg': '端口号格式有误'})
        ftp_username = arg.get('ftp_username')
        ftp_password = arg.get('ftp_password')
        ftp_schema = arg.get('ftp_schema')  # ftp模式
        if not all([ftp_name, ftp_ip, ftp_port, ftp_username, ftp_password, ftp_schema]):
            return JsonResponse({'code': 1, 'msg': '必填项不能为空'})
        source = TdsdFtpSource.objects.filter(ftp_id=ftp_id, isdelete=0)
        if len(source) == 1:  # 这条数据源存在，可以进行变更操作
            try:
                with transaction.atomic():  # 开启事务，失败会自动回滚，并执行except中的代码
                    source.update(
                        ftp_name=ftp_name,
                        ftp_ip=ftp_ip,
                        ftp_port=ftp_port,
                        ftp_username=ftp_username,
                        ftp_password=ftp_password,
                        ftp_schema=ftp_schema,
                    )
                    return JsonResponse({'code': 0, 'msg': 'Success'})
            except Exception as e:
                return JsonResponse({'code': 2, 'msg': str(e)})
        else:
            return JsonResponse({'code': 1, 'msg': '数据资源不存在'})


@util.user_login
def source_info(request):
    """数据源信息的增删改查，用户登录后方可看到自己名下的数据源信息"""
    user_id = request.session.get('user_id')
    level_id = request.session.get('level_id')
    # 超级管理可以看到所有的数据源，因此需要拿到level_id
    if request.method == 'POST':
        # 保存数据源信息到数据库中
        arg = request.POST
        source_name = arg.get('source_name')
        # 对资源名称格式进行验证
        if not fv.verify_source_name(source_name):
            return JsonResponse({'code': 1, 'msg': '数据源名称只能为数字字母汉字，且不能以数字开头'})
        # 数据源名称和用户联合唯一
        _source = TdsdDataSource.objects.filter(isdelete=0, user_id=user_id, source_name=source_name)
        if _source.count() > 0:  # 说明此用户名下已有此数据源名称
            return JsonResponse({'code': 1, 'msg': '数据源名称已存在'})
        host_ip = arg.get('host_ip')  # 必填
        db_ip = arg.get('db_ip')  # 必填
        # 对ip地址进行验证
        if not fv.verify_ip(db_ip, host_ip):
            return JsonResponse({'code': 1, 'msg': 'ip地址格式有误'})
        port = arg.get('port')  # 必填
        if not fv.verify_port(port):
            return JsonResponse({'code': 1, 'msg': '端口号格式有误'})
        db_username = arg.get('db_username')  # 数据库的用户名，必填
        db_password = arg.get('db_password')  # 数据库的密码，必填
        db_name = arg.get('db_name')  # 必填
        if db_name == '' or db_ip == '' or db_username == '' or db_password == '':
            return JsonResponse({'code': 1, 'msg': '必填项不能为空'})
        db_cate = arg.get('db_cate')  # 哪个数据库，mysql or oracle
        if db_cate == '0':
            db_cate = 'MySQL'
            conn_url = None
        elif db_cate == '1':
            db_cate = 'Oracle'
            conn_url = db_username + '/' + db_password + '@' + db_ip + ':' + port + '/' + db_name
        else:
            return JsonResponse({'code': 1, 'msg': '数据库类型有误'})
        schema = arg.get('schema') if arg.get('schema') else None
        jdbc = arg.get('jdbc') if arg.get('jdbc') else None  # TODO 如果jdbc存在，就按jdbc方式请求，暂时不写此种方式
        desc = arg.get('desc') if arg.get('desc') else None
        try:
            with transaction.atomic():  # 开启事务，失败会自动回滚，并执行except中的代码
                source = TdsdDataSource.objects.create(
                    source_name=source_name,
                    host_ip=host_ip,
                    db_ip=db_ip,
                    port=port,
                    db_cate=db_cate,
                    username=db_username,
                    password=db_password,
                    sid=db_name,  # sid=db_name
                    schema=schema,
                    conn_url=conn_url,
                    jdbc_url=jdbc,
                    desc=desc,
                    user_id=user_id,  # 登录之后才有值，否则为None
                )
                return JsonResponse({'code': 0, 'msg': 'Success'})
        except Exception as e:
            return JsonResponse({'code': 2, 'msg': str(e)})
    elif request.method == 'GET':
        # 获取所有数据源列表返回前端展示，数据表格的异步请求，根据用户level_id等级显示不同内容，普通用户看到自己创建的，超级管理员看到所有的
        arg = request.GET
        search_info = arg.get('search_info')  # 搜索内容，数据源名称
        current_page = arg.get('page')  # 当前页码
        limit = arg.get('limit')  # 限制条目数
        if level_id == 0:  # 超级管理员
            if search_info:
                # 处理搜索内容的数据返回
                # 超级管理员不当看到已经逻辑删除的数据源
                sources_obj = TdsdDataSource.objects.filter(isdelete=0, source_name__contains=search_info).order_by(
                    '-update_time')
            else:
                sources_obj = TdsdDataSource.objects.filter(isdelete=0).order_by('-update_time')
        else:
            if search_info:
                sources_obj = TdsdDataSource.objects.filter(isdelete=0, source_name__contains=search_info, user_id=user_id).order_by(
                    '-update_time')
            else:
                sources_obj = TdsdDataSource.objects.filter(isdelete=0, user_id=user_id).order_by(
                    '-update_time')  # 根据用户id获取名下未删除的数据源信息
        count = sources_obj.count()
        if count > 0:  # 有数据，返回
            paginator = Paginator(sources_obj, limit)
            # 得到当前页的数据
            page = paginator.page(current_page)
            data = []
            for i, source in enumerate(page.object_list):
                item = dict()
                # role_id和dir_id的用途是编辑时数据回显
                item['id'] = i + 1  # 用于前端展示的编号
                item['source_id'] = source.id  # 数据库真实id编号
                item['source_name'] = source.source_name
                item['host_ip'] = source.host_ip
                item['db_ip'] = source.db_ip
                item['port'] = source.port
                item['db_cate'] = source.db_cate
                if source.db_cate == 'MySQL':
                    item['db_cate_value'] = '0'  # 点击编辑按钮，数据回显时，根据对应的value显示mysql或oracle
                elif source.db_cate == 'Oracle':
                    item['db_cate_value'] = '1'
                item['sid'] = source.sid
                item['schema'] = source.schema
                item['desc'] = source.desc
                item['jdbc'] = source.jdbc_url
                user_id_source = source.user_id  # 每条资源所属的用户id
                # 每条资源所属的用户名，有可能用户id不存在，清库的情况
                user = TdsdUser.objects.filter(id=user_id_source)
                username = user[0].username if len(user) == 1 else None
                item['username'] = username  # 用户的用户名
                item['db_username'] = source.username  # 数据库的用户名
                item['db_password'] = source.password  # 数据库的密码
                data.append(item)
            resp = {"code": 0, "count": count, "data": data}
        else:  # 没有数据
            resp = {'code': 1, 'msg': '暂无数据源!'}
        return JsonResponse(resp)
    elif request.method == 'DELETE':
        # 逻辑删除数据源信息
        arg = request_body_serialize(request)
        source_id = arg.get('source_id')
        # 从数据源表中找到对应的信息,然后逻辑删除
        source = TdsdDataSource.objects.filter(id=source_id, isdelete=0)  # 已经删除过不能再次删除
        if len(source) == 1:
            try:
                with transaction.atomic():
                    source.update(isdelete=1)
                    code = 0
                    msg = 'Success'
            except Exception as e:
                code = 2  # 数据库操作异常
                msg = str(e)
        else:
            msg = '数据源不存在'
            code = 1
        resp = {'code': code, 'msg': msg}
        return JsonResponse(resp)
    elif request.method == 'PUT':
        # 数据源更改操作
        arg = request_body_serialize(request)
        source_name = arg.get('source_name')
        if not fv.verify_source_name(source_name):
            return JsonResponse({'code': 1, 'msg': '数据源名称只能为数字字母汉字，且不能以数字开头'})
        source_id = arg.get('source_id')
        host_ip = arg.get('host_ip')
        db_ip = arg.get('db_ip')
        if not fv.verify_ip(db_ip):
            return JsonResponse({'code': 1, 'msg': 'ip地址格式有误'})
        port = arg.get('port')
        if not fv.verify_port(port):
            return JsonResponse({'code': 1, 'msg': '端口号格式有误'})
        db_username = arg.get('db_username')  # 数据库的用户名
        db_password = arg.get('db_password')  # 数据库的密码
        db_name = arg.get('db_name')
        if db_name == '' or db_ip == '' or db_username == '' or db_password == '':
            return JsonResponse({'code': 1, 'msg': '必填项不能为空'})
        db_cate_value = arg.get('db_cate_value')  # 哪个数据库，mysql or oracle
        source = TdsdDataSource.objects.filter(id=source_id, isdelete=0)
        if len(source) == 1:
            # 说明有数据
            if db_cate_value == '0':
                db_cate = 'MySQL'
                conn_url = None
            elif db_cate_value == '1':
                db_cate = 'Oracle'
                db_password = db_password if db_password else source[0].password
                conn_url = db_username + '/' + db_password + '@' + db_ip + ':' + port + '/' + db_name
            else:  # 恶意请求
                return JsonResponse({'code': 1, 'msg': '数据库类型不存在'})
            schema = arg.get('schema') if arg.get('schema') else None
            jdbc = arg.get('jdbc') if arg.get('jdbc') else None
            desc = arg.get('desc') if arg.get('desc') else None
            try:
                with transaction.atomic():  # 开启事务，失败会自动回滚，并执行except中的代码
                    source.update(
                        source_name=source_name,
                        host_ip=host_ip,
                        db_ip=db_ip,
                        port=port,
                        db_cate=db_cate,
                        username=db_username,
                        password=db_password,
                        sid=db_name,  # sid=db_name
                        schema=schema,
                        conn_url=conn_url,
                        jdbc_url=jdbc,
                        desc=desc,
                        user_id=user_id,  # 登录之后才有值，否则为None
                    )
                    return JsonResponse({'code': 0, 'msg': 'Success'})
            except Exception as e:
                return JsonResponse({'code': 2, 'msg': str(e)})
        else:
            return JsonResponse({'code': 1, 'msg': '数据资源不存在'})


def conn_oracle(conn_url, data):
    """连接oracle数据库"""
    try:
        # 连接错误，不会有conn对象
        conn = cx_Oracle.connect(conn_url)
    except Exception as e:
        data['code'] = 2
        data['msg'] = str(e)
    else:
        # TODO 抛出[WinError 10053] 你的主机中的软件中止了一个已建立的连接。异常的原因应当是没有关闭连接
        conn.close()  # 测试完成，关闭连接
        data['msg'] = 'Success'
        data['code'] = 0
    return data


def conn_mysql(host, user, password, database, port, data):
    """连接mysql数据库"""
    try:
        # 连接错误，不会有conn对象
        conn = pymysql.connect(host=host, user=user, password=password,
                               database=database, port=port,
                               charset='utf8')  # 默认连接10秒超时
    except Exception as e:
        data['code'] = 2
        data['msg'] = str(e)
    else:
        conn.close()  # 测试无异常，测试完成，关闭连接
        data['msg'] = 'Success'
        data['code'] = 0
    return data


def conn_ftp(host='', port=21, user='', passwd='', schema=0):  # 打开FTP
    try:
        ftp = FTP()
        if schema == 0:  # 主动模式
            ftp.set_pasv(False)  # 默认开启的被动模式
        ftp.connect(host, port)
        ftp.login(user, passwd)  # 登录，如果匿名登录则用空串代替即可
        ftp.quit()
        return 0
    except Exception as e:
        return str(e)


@util.user_login
def connection(request):
    """数据库连接测试"""
    if request.method == 'GET':
        # 数据源信息保存之后的测试
        arg = request.GET
        source_id = arg.get('source_id')
        source = TdsdDataSource.objects.filter(id=source_id, isdelete=0)  # 得到具体的数据源记录
        if source.count() == 1:  # 防止恶意访问，使用get会抛出异常
            source = source[0]
            db_cate = source.db_cate
            data = dict()  # 返回的成功与否的信息
            if db_cate == 'Oracle':
                conn_url = source.conn_url  # oracle直接取出url直接连接
                ret = conn_oracle(conn_url, data)
            elif db_cate == 'MySQL':  # mysql需要取出具体的几项数据
                db_ip = source.db_ip
                db_username = source.username
                db_password = source.password
                db_name = source.sid  # 数据库名
                port = source.port
                ret = conn_mysql(db_ip, db_username, db_password, db_name, int(port), data)
            return JsonResponse(ret)
        else:
            return JsonResponse({'code': 1, 'msg': '资源不存在'})
    elif request.method == 'POST':
        # 数据源信息保存之前的测试
        arg = request.POST
        # 只提取和连接数据库相关的信息
        db_cate = arg.get('db_cate')  # 0 mysql 1 oracle
        db_cate_value = arg.get('db_cate_value')  # 编辑页面中数据库类别name是db_cate_value
        db_ip = arg.get('db_ip')
        port = arg.get('port')  # port必须是数字
        db_name = arg.get('db_name')  # 测试的数据库名
        db_username = arg.get('db_username')  # 数据库用户名
        db_password = arg.get('db_password')
        jdbc_url = arg.get('jdbc')
        data = dict()
        if port.isdigit():
            if jdbc_url == "":
                # 按照python的连接方式连接，否则按照jdbc
                if db_cate == '1' or db_cate_value == '1':  # oracle
                    conn_url = db_username + '/' + db_password + '@' + db_ip + ':' + port + '/' + db_name
                    ret = conn_oracle(conn_url, data)
                elif db_cate == '0' or db_cate_value == '0':  # mysql
                    ret = conn_mysql(db_ip, db_username, db_password, db_name, int(port), data)
            else:
                ret = {'code': 1, 'msg': 'jdbc服务暂未提供'}
            return JsonResponse(ret)
        else:
            return JsonResponse({'code': 1, 'msg': '端口填写有误，请重新输入'})


def ftp_connection(request):
    if request.method == 'GET':
        # 数据源信息保存之后的测试
        arg = request.GET
        ftp_id = arg.get('ftp_id')
        source = TdsdFtpSource.objects.filter(ftp_id=ftp_id, isdelete=0).first()  # 得到具体的数据源记录
        if source:
            ftp_ip = source.ftp_ip
            ftp_port = source.ftp_port
            ftp_username = source.ftp_username
            ftp_password = source.ftp_password
            ftp_schema = source.ftp_schema
            ret = conn_ftp(ftp_ip, int(ftp_port), ftp_username, ftp_password, ftp_schema)
            if ret == 0:
                return JsonResponse({'code': 0, 'msg': 'Success'})
            else:
                return JsonResponse({'code': 1, 'msg': ret})
        else:
            return JsonResponse({'code': 1, 'msg': '资源不存在'})
    elif request.method == 'POST':
        # 数据源信息保存之前的测试
        arg = request.POST
        ftp_ip = arg.get('ftp_ip')
        ftp_port = arg.get('ftp_port')
        ftp_username = arg.get('ftp_username')
        ftp_password = arg.get('ftp_password')  # port必须是数字
        ftp_schema = arg.get('ftp_schema')
        if ftp_port.isdigit():
            ret = conn_ftp(ftp_ip, int(ftp_port), ftp_username, ftp_password, ftp_schema)
            if ret == 0:
                return JsonResponse({'code': 0, 'msg': 'Success'})
            else:
                return JsonResponse({'code': 1, 'msg': ret})
        else:
            return JsonResponse({'code': 1, 'msg': '端口填写有误，请重新输入'})
